package es.ats.codington.festival.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import es.ats.codington.festival.domain.Event;

/**
 * The Class EventDbQuery.
 */
public class EventDbQuery {

	/** The adress. */
	static String adress = "src/es/ats/codington/festival/utils/DBJson.json";

	/**
	 * Inser in event.
	 *
	 * @param idEvent the id event
	 * @param idVisitor the id visitor
	 * @param con the con
	 * @param query the query
	 * @return true, if successful
	 */
	public static boolean inserInEvent(int idEvent, int idVisitor, Connection con, String query) {
		int rs=0; 

		
	    try{
	    	PreparedStatement pStmt = null; 
	    	pStmt = con.prepareStatement(query);
			

			pStmt.setInt(1, idVisitor);
			pStmt.setInt(2, idEvent);

			
			con.setAutoCommit(false);
			rs = pStmt.executeUpdate();
			con.commit();	

			
			}catch(Exception e){
			System.out.println(e.getMessage());
			}
	    
		if (rs==1) return true;
		else return false;	
	
	}
	
	
	/**
	 * Delete from event.
	 *
	 * @param idEvent the id event
	 * @param idVisitor the id visitor
	 * @param con the con
	 * @param query the query
	 * @return true, if successful
	 */
	public static boolean deleteFromEvent(int idEvent, int idVisitor, Connection con, String query) {
		int rs=0; 

		
	    try{
	    	PreparedStatement pStmt = null; 
	    	pStmt = con.prepareStatement(query);
			

			pStmt.setInt(1, idVisitor);
			pStmt.setInt(2, idEvent);

			
			con.setAutoCommit(false);
			rs = pStmt.executeUpdate();
			con.commit();	

			
			}catch(Exception e){
			System.out.println(e.getMessage());
			}
	    
		if (rs==1) return true;
		else return false;	
	}



	/**
	 * Ins event table.
	 *
	 * @param conec the conec
	 * @param event the event
	 * @param chain the chain
	 * @return the int
	 * @throws Exception the exception
	 */
	public static int insEventTable(Connection conec, Event event, String chain) throws Exception {
		int rs=0;
		
	    try{
	    	PreparedStatement pStmt = null; 
	    	pStmt = conec.prepareStatement(chain);
						
			pStmt.setString(1,event.getName());
			pStmt.setString(2,event.getDescription());
			pStmt.setInt(3,event.getDuration());
			pStmt.setString(4,event.getTypeEvent());
			pStmt.setInt(5,event.getCapacity());
			pStmt.setInt(6, event.getIdPlace());

			conec.setAutoCommit(false);
			
			//Executing this query using executeUpdate()
			rs = pStmt.executeUpdate();
			
			//Committing
			conec.commit();	
	
			/*	
			ResultSet keys = event1.getGeneratedKeys();
		    keys.next();
		    int key = keys.getInt(1);
		 	*/		
	
			}catch(Exception e){
			System.out.println(e.getMessage());
			}
	    
			return rs;
	}
	
	/**
	 * Del event table.
	 *
	 * @param conec the conec
	 * @param idEvent the id event
	 * @param chain the chain
	 * @return the int
	 * @throws Exception the exception
	 */
	public static int delEventTable(Connection conec, int idEvent, String chain) throws Exception {
		int rs=0;
		
	    try{
	    	//Creating a PreparedStatement with a query "delete from Employee where idEvent = ?" 
			PreparedStatement pStmt = conec.prepareStatement(chain);
			
			//Replacing ? with actual value 
			pStmt.setInt(1, idEvent);
			
			//Executing this query using executeUpdate()
			rs = pStmt.executeUpdate();
			
			//Committing
			conec.commit();	
	
			
			//Print the number of rows deleted
			//System.out.println(rs + " record was deleted");
			
			
	
			}catch(Exception e){
			System.out.println(e.getMessage());
			}
	    
			return rs;
	}
	
	/**
	 * Up event table.
	 *
	 * @param event the event
	 * @param conec the conec
	 * @param pStmt the stmt
	 * @return the int
	 */
	public static int upEventTable(Event event, Connection conec, PreparedStatement pStmt) {
		int rows=0;
		
		  try{				
				pStmt.setString(1,event.getName());
				pStmt.setString(2,event.getDescription());
				pStmt.setInt(3,event.getDuration());
				pStmt.setString(4,event.getTypeEvent());
				pStmt.setInt(5,event.getCapacity());
				pStmt.setInt(6, event.getIdPlace());
				pStmt.setInt(7, event.getId());

				//Executing this query using executeUpdate()
				rows = pStmt.executeUpdate();
				
				conec.commit();
				
				//Print the number of rows updated
				//System.out.println(rows + " record was updated");
				
		
				}catch(Exception e){
				System.out.println(e.getMessage());
				}
		
		return rows;
	}

	/**
	 * Count in event.
	 *
	 * @param idVisitor the id visitor
	 * @param conec the conec
	 * @param query the query
	 * @param events the events
	 * @return the int
	 */
	public static int countInEvent(int idVisitor, Connection conec, String query, int[] events) {
		int rs=0;		
	    try{
	    	PreparedStatement pStmt = null; 
	    	pStmt = conec.prepareStatement(query);			
			pStmt.setInt(1,idVisitor);

			conec.setAutoCommit(false);
	
			ResultSet resul = pStmt.executeQuery();
			
			while(resul.next()){
				events[rs] = resul.getInt("EventId");
				rs++;
			}			
			
			conec.commit();			
			}catch(Exception e){
			System.out.println(e.getMessage());
			}    
		return rs;
	}


/**
 * Count in event.
 *
 * @param idVisitor the id visitor
 * @param conec the conec
 * @param query the query
 * @return the int
 */
public static int countInEvent(int idVisitor, Connection conec, String query) {
	int rs=0;
	
    try{
    	PreparedStatement pStmt = null; 
    	pStmt = conec.prepareStatement(query);			
		pStmt.setInt(1,idVisitor);
/*
		conec.setAutoCommit(false);
		rs = pStmt.executeUpdate();		
		conec.commit();	
*/
		ResultSet resul = pStmt.executeQuery();
		
		//Committing
		conec.commit();	
		
		while(resul.next()){	
			rs++;
		}
		
		
		}catch(Exception e){
		System.out.println(e.getMessage());
		}   
	return rs;
}



/**
 * Gets the all events by one.
 *
 * @param conec the conec
 * @param query the query
 * @param event the event
 * @param i the i
 * @return the all events by one
 */
public static Boolean getAllEventsByOne(Connection conec, String query, ArrayList<Event> event, int i) {
boolean rs=false;
	
    try{
    	PreparedStatement pStmt = null; 
    	pStmt=conec.prepareStatement(query);			
		

		conec.setAutoCommit(false);

		ResultSet resul = pStmt.executeQuery();
		resul.next();
		Event ev = new Event(i, resul.getString("Name"), resul.getString("Description"),resul.getInt("Duration"), resul.getString("EventType"), resul.getInt("Capacity"), 2);
		ev.setId(i);
		
		event.add(ev);		
		conec.commit();	


		}catch(Exception e){
		System.out.println(e.getMessage());
		}
        rs = true;
		return rs;
}


/**
 * Gets the all events by one.
 *
 * @param conec the conec
 * @param query the query
 * @param event the event
 * @param i the i
 * @param iter the iter
 * @return the all events by one
 */
public static Boolean getAllEventsByOne(Connection conec, String query, ArrayList<Event> event, int i, int iter) {
boolean rs=false;
	
    try{
    	PreparedStatement pStmt = null; 
    	pStmt=conec.prepareStatement(query);			
		

		conec.setAutoCommit(false);

		ResultSet resul = pStmt.executeQuery();
		for (int j=0; j<=iter; j++) resul.next();
		
		Event ev = new Event(i, resul.getString("Name"), resul.getString("Description"),resul.getInt("Duration"), resul.getString("EventType"), resul.getInt("Capacity"), 2);
		ev.setId(i);
		
		event.add(ev);		
		conec.commit();	


		}catch(Exception e){
		System.out.println(e.getMessage());
		}
        rs = true;
		return rs;
}




/**
 * Count events.
 *
 * @param idV the id v
 * @param con the con
 * @param query the query
 * @return the int
 */
public static int countEvents(int idV, Connection con, String query) {
int rs=0;
	
    try{
    	PreparedStatement pStmt = null; 
    	pStmt = con.prepareStatement(query);			

		ResultSet resul = pStmt.executeQuery();
		
		//Committing
		con.commit();	
		
		while(resul.next()){	
			rs++;
		}
		
		
		}catch(Exception e){
		System.out.println(e.getMessage());
		}   
	return rs;

}



/**
 * Obtain all event.
 *
 * @param conec the conec
 * @param chain the chain
 * @return the array list
 */
public static ArrayList<Event> obtainAllEvent(Connection conec, String chain) {
	// TODO Auto-generated method stub
	
	ArrayList<Event> eve = new ArrayList<Event>();

	try{
		
		//Creating a PreparedStatement with a query "select * from Event" 
		PreparedStatement pStmt = conec.prepareStatement(chain);
		
		//Executing this query using executeQuery()
		ResultSet rs = pStmt.executeQuery();
		

		
		while(rs.next())
		{
			Event evento = new Event();

			evento.setId(rs.getInt("idEvent"));
			evento.setName(rs.getString("Name"));
			evento.setDescription(rs.getString("Description"));
			evento.setDuration(rs.getInt("Duration"));
			evento.setTypeEvent(rs.getString("EventType"));
			evento.setCapacity(rs.getInt("Capacity"));
			evento.setIdPlace(rs.getInt("PlaceId"));

			eve.add(evento);

		}
		//Closing the connection
		conec.close();
		
	}catch (Exception e){
		System.out.println(e.getMessage());
	}
	
	return eve;

}




/**
 * Countn events.
 *
 * @param idV the id v
 * @param con the con
 * @param query the query
 * @param events the events
 * @return the int
 */
public static int countnEvents(int idV, Connection con, String query,int[] events) {
	int rs=0;		
    try{
    	PreparedStatement pStmt = null; 
    	pStmt = con.prepareStatement(query);			

		con.setAutoCommit(false);

		ResultSet resul = pStmt.executeQuery();
		
		while(resul.next()){
			events[rs] = resul.getInt("idEvent");
			rs++;
		}			
		
		con.commit();			
		}catch(Exception e){
		System.out.println(e.getMessage());
		}    
	return rs;
}



/**
 * Search by id.
 *
 * @param conec the conec
 * @param chain the chain
 * @param idEvent the id event
 * @return the event
 */
public static Event searchById(Connection conec, String chain, int idEvent) {
	Event evento = new Event();
	try{
		
		//Creating a PreparedStatement with a query "select * from Event" 
		PreparedStatement pStmt = conec.prepareStatement(chain);
	
		pStmt.setInt(1, idEvent);
		
		conec.setAutoCommit(false);
		
		ResultSet rs = pStmt.executeQuery();
		
		while(rs.next())
		{

			evento.setId(rs.getInt("idEvent"));
			evento.setName(rs.getString("Name"));
			evento.setDescription(rs.getString("Description"));
			evento.setDuration(rs.getInt("Duration"));
			evento.setTypeEvent(rs.getString("EventType"));
			evento.setCapacity(rs.getInt("Capacity"));
			evento.setIdPlace(rs.getInt("PlaceId"));
		}
		//Closing the connection
		conec.close();
		
	}catch (Exception e){
		System.out.println(e.getMessage());
	}
	
	return evento;
}
}

